﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using ConnectLib.DAO;
using MSSQLProvider.DataBase;

namespace ConnectLib.DAL
{
    public class InvoiceDal : MSSQLDataAccess
    {
        private static InvoiceDal _instance = new InvoiceDal();

        public static InvoiceDal Instance
        {
            get
            {
                return _instance;
            }
        }

        public InvoiceDal()
        {
        }

        public bool Exist(string invcode)
        {
            if (ExecuteScalar(string.Format("SELECT InvId FROM [Invoice] WHERE InvId = '{0}'", invcode)) != null)
            {
                return true;
            }

            return false;
        }

        public int AddInvoice(InvoiceDao inv)
        {
            try
            {
                if (Exist(inv.InvId))
                {
                    return 0;
                }

                List<string> arrFielName = new List<string>();
                List<string> arrParamName = new List<string>();
                List<object> arrValue = new List<object>();

                arrFielName.Add("InvId");
                arrParamName.Add("@InvId");
                arrValue.Add(inv.InvId);

                arrFielName.Add("StaffName");
                arrParamName.Add("@StaffName");
                arrValue.Add(inv.StaffName);

                arrFielName.Add("Sender");
                arrParamName.Add("@Sender");
                arrValue.Add(inv.Sender);

                arrFielName.Add("StartDate");
                arrParamName.Add("@StartDate");
                arrValue.Add(inv.StartDate);

                arrFielName.Add("StartPlace");
                arrParamName.Add("@StartPlace");
                arrValue.Add(inv.StartPlace);

                arrFielName.Add("Receiver");
                arrParamName.Add("@Receiver");
                arrValue.Add(inv.Receiver);

                arrFielName.Add("EndDate");
                arrParamName.Add("@EndDate");
                arrValue.Add(inv.EndDate);

                arrFielName.Add("EndPlace");
                arrParamName.Add("@EndPlace");
                arrValue.Add(inv.EndPlace);

                arrFielName.Add("CustomerNote");
                arrParamName.Add("@CustomerNote");
                arrValue.Add(inv.CustomerNote);

                arrFielName.Add("ServiceName");
                arrParamName.Add("@ServiceName");
                arrValue.Add(inv.ServiceName);

                arrFielName.Add("Price");
                arrParamName.Add("@Price");
                arrValue.Add(inv.Price);

                arrFielName.Add("Postman");
                arrParamName.Add("@Postman");
                arrValue.Add(inv.Postman);

                arrFielName.Add("ProductName");
                arrParamName.Add("@ProductName");
                arrValue.Add(inv.ProductName);

                arrFielName.Add("Dimension");
                arrParamName.Add("@Dimension");
                arrValue.Add(inv.Dimension);

                arrFielName.Add("Weight");
                arrParamName.Add("@Weight");
                arrValue.Add(inv.Weight);

                arrFielName.Add("ProductNote");
                arrParamName.Add("@ProductNote");
                arrValue.Add(inv.ProductNote);

                arrFielName.Add("Release");
                arrParamName.Add("@Release");
                arrValue.Add(inv.Release);
                
                var queryBuider = new StringBuilder();
                queryBuider.Append("INSERT INTO [Invoice] (");
                int i = 0;

                for (; i < arrFielName.Count; i++)
                {
                    queryBuider.Append(arrFielName[i]);
                    if (i < arrFielName.Count - 1)
                        queryBuider.Append(",");
                }
                queryBuider.Append(")");
                queryBuider.Append(" VALUES (");
                i = 0;

                for (; i < arrParamName.Count; i++)
                {
                    queryBuider.Append(arrParamName[i]);
                    if (i < arrParamName.Count - 1)
                        queryBuider.Append(",");
                }
                queryBuider.Append(")");
                
                return ExecuteNonQuery(queryBuider.ToString(), arrParamName, arrValue);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public ResQuickInvoice AddQuickInvoice(string[] invoices, string staff, DateTime importtime, string branchname, byte release)
        {
            int status = 0;
            string errinv = string.Empty;
            string notexistinv = string.Empty;
            
            try
            {
                foreach (string invid in invoices)
                {
                    InvoiceDao inv = new InvoiceDao();

                    inv.InvId = invid.Replace(" ", "");
                    inv.StaffName = staff;
                    inv.StartDate = importtime;
                    inv.StartPlace = branchname;
                    inv.ServiceName = ServiceDal.Instance.GetDefaultServiceName();
                    inv.Release = release;

                    switch (AddInvoice(inv))
                    {
                        case 1: //add ok
                            TrackingDao tracking = new TrackingDao();
                            tracking.InvId = invid;
                            tracking.StaffName = staff;
                            tracking.LocationDate = importtime;
                            tracking.LocationAt = branchname;
                            tracking.Status = release; //import

                            TrackingDal.Instance.AddTracking(tracking);
                            break;
                        case 0: //inv exsit
                            status = (status | 2);
                            notexistinv += (invid + ",");
                            break;
                        default: //error
                            status = (status | 1);
                            errinv += (errinv + ",");
                            break;
                    }
                }

                if (notexistinv.Length > 0)
                {
                    notexistinv.Remove(notexistinv.Length - 1);
                }

                if (errinv.Length > 0)
                {
                    errinv.Remove(errinv.Length - 1);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return new ResQuickInvoice
            {
                Status = status,
                ErrInv = errinv,
                NotExistInv = notexistinv
            };
        }
        
        public int UpdateInvoice(InvoiceDao inv)
        {
            try
            {
                if (!Exist(inv.InvId))
                {
                    return 0;
                }

                List<string> arrFielName = new List<string>();
                List<string> arrParamName = new List<string>();
                List<object> arrValue = new List<object>();

                arrFielName.Add("StaffName");
                arrParamName.Add("@StaffName");
                arrValue.Add(inv.StaffName);

                arrFielName.Add("Sender");
                arrParamName.Add("@Sender");
                arrValue.Add(inv.Sender);

                arrFielName.Add("StartDate");
                arrParamName.Add("@StartDate");
                arrValue.Add(inv.StartDate);

                arrFielName.Add("StartPlace");
                arrParamName.Add("@StartPlace");
                arrValue.Add(inv.StartPlace);

                arrFielName.Add("Receiver");
                arrParamName.Add("@Receiver");
                arrValue.Add(inv.Receiver);

                arrFielName.Add("EndDate");
                arrParamName.Add("@EndDate");
                arrValue.Add(inv.EndDate);

                arrFielName.Add("EndPlace");
                arrParamName.Add("@EndPlace");
                arrValue.Add(inv.EndPlace);

                arrFielName.Add("CustomerNote");
                arrParamName.Add("@CustomerNote");
                arrValue.Add(inv.CustomerNote);

                arrFielName.Add("ServiceName");
                arrParamName.Add("@ServiceName");
                arrValue.Add(inv.ServiceName);

                arrFielName.Add("Price");
                arrParamName.Add("@Price");
                arrValue.Add(inv.Price);

                arrFielName.Add("Postman");
                arrParamName.Add("@Postman");
                arrValue.Add(inv.Postman);

                arrFielName.Add("ProductName");
                arrParamName.Add("@ProductName");
                arrValue.Add(inv.ProductName);

                arrFielName.Add("Dimension");
                arrParamName.Add("@Dimension");
                arrValue.Add(inv.Dimension);

                arrFielName.Add("Weight");
                arrParamName.Add("@Weight");
                arrValue.Add(inv.Weight);

                arrFielName.Add("ProductNote");
                arrParamName.Add("@ProductNote");
                arrValue.Add(inv.ProductNote);

                arrFielName.Add("Release");
                arrParamName.Add("@Release");
                arrValue.Add(inv.Release);

                arrFielName.Add("InvId");
                arrParamName.Add("@InvId");
                arrValue.Add(inv.InvId);
                
                var queryBuider = new StringBuilder();
                queryBuider.Append("UPDATE [Invoice] SET ");
                int i = 0;

                for (; i < arrFielName.Count; i++)
                {
                    if (i != arrFielName.Count - 1)
                    {
                        queryBuider.Append(arrFielName[i]);
                        queryBuider.Append("=");
                        queryBuider.Append(arrParamName[i]);
                        if (i < arrFielName.Count - 2)
                            queryBuider.Append(",");
                    }
                    else
                    {
                        queryBuider.Append(" WHERE ");
                        queryBuider.Append(arrFielName[i]);
                        queryBuider.Append("=");
                        queryBuider.Append(arrParamName[i]);
                    }
                }

                return ExecuteNonQuery(queryBuider.ToString(), arrParamName, arrValue);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int UpdateQuickInvoice(InvoiceDao inv)
        {
            try
            {
                List<string> arrFielName = new List<string>();
                List<string> arrParamName = new List<string>();
                List<object> arrValue = new List<object>();

                arrFielName.Add("StaffName");
                arrParamName.Add("@StaffName");
                arrValue.Add(inv.StaffName);

                arrFielName.Add("StartDate");
                arrParamName.Add("@StartDate");
                arrValue.Add(inv.StartDate);

                arrFielName.Add("StartPlace");
                arrParamName.Add("@StartPlace");
                arrValue.Add(inv.StartPlace);

                arrFielName.Add("Release");
                arrParamName.Add("@Release");
                arrValue.Add(inv.Release);

                arrFielName.Add("InvId");
                arrParamName.Add("@InvId");
                arrValue.Add(inv.InvId);

                var queryBuider = new StringBuilder();
                queryBuider.Append("UPDATE [Invoice] SET ");
                int i = 0;

                for (; i < arrFielName.Count; i++)
                {
                    if (i != arrFielName.Count - 1)
                    {
                        queryBuider.Append(arrFielName[i]);
                        queryBuider.Append("=");
                        queryBuider.Append(arrParamName[i]);
                        if (i < arrFielName.Count - 2)
                            queryBuider.Append(",");
                    }
                    else
                    {
                        queryBuider.Append(" WHERE ");
                        queryBuider.Append(arrFielName[i]);
                        queryBuider.Append("=");
                        queryBuider.Append(arrParamName[i]);
                    }
                }

                return ExecuteNonQuery(queryBuider.ToString(), arrParamName, arrValue);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int UpdateTransitInvoice(InvoiceDao inv)
        {
            try
            {
                List<string> arrFielName = new List<string>();
                List<string> arrParamName = new List<string>();
                List<object> arrValue = new List<object>();

                arrFielName.Add("StaffName");
                arrParamName.Add("@StaffName");
                arrValue.Add(inv.StaffName);

                arrFielName.Add("StartDate");
                arrParamName.Add("@StartDate");
                arrValue.Add(inv.StartDate);

                arrFielName.Add("StartPlace");
                arrParamName.Add("@StartPlace");
                arrValue.Add(inv.StartPlace);

                arrFielName.Add("Release");
                arrParamName.Add("@Release");
                arrValue.Add(inv.Release);

                arrFielName.Add("InvId");
                arrParamName.Add("@InvId");
                arrValue.Add(inv.InvId);

                var queryBuider = new StringBuilder();
                queryBuider.Append("UPDATE [Invoice] SET ");
                int i = 0;

                for (; i < arrFielName.Count; i++)
                {
                    if (i != arrFielName.Count - 1)
                    {
                        queryBuider.Append(arrFielName[i]);
                        queryBuider.Append("=");
                        queryBuider.Append(arrParamName[i]);
                        if (i < arrFielName.Count - 2)
                            queryBuider.Append(",");
                    }
                    else
                    {
                        queryBuider.Append(" WHERE ");
                        queryBuider.Append(arrFielName[i]);
                        queryBuider.Append("=");
                        queryBuider.Append(arrParamName[i]);
                    }
                }

                return ExecuteNonQuery(queryBuider.ToString(), arrParamName, arrValue);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool DeleteInvoice(string invid)
        {
            return ExecuteNonQuery(string.Format("DELETE FROM [Invoice] WHERE InvId = '{0}'", invid));
        }

        public bool DeleteInvoices(string invids)
        {
            return ExecuteNonQuery(string.Format("DELETE FROM [Invoice] WHERE [InvId] in ({0})", invids));
        }

        public int TotalInvoices(string invcode, string customer, byte release)
        {
            string sender = "1=1";
            string invid = "1=1";

            if (!string.IsNullOrEmpty(customer))
            {
                sender = "[Sender] = '" + customer + "'";
            }

            if (!string.IsNullOrEmpty(invcode))
            {
                invid = "[InvId] = '" + invcode + "'";
            }

            SqlDataReader dr = null;
            int total = 0;

            switch (release)
            {
                case 0://not release yet
                    dr = ExecuteReader("SELECT COUNT(InvId) AS [counter] FROM [Invoice] WITH (NOLOCK) WHERE " + sender + " AND " + invid +
                                  " AND [Release] = 0");
                    try
                    {
                        while (dr.Read())
                        {
                            total = Convert.ToInt32(dr[0]);
                        }
                    }
                    catch (Exception)
                    {
                        throw;
                    }
                    finally
                    {
                        if (dr != null)
                        {
                            dr.Close();
                        }
                    }
                    
                    break;
                case 1://release
                    dr = ExecuteReader("SELECT COUNT(InvId) AS [counter] FROM [Invoice] WITH (NOLOCK) WHERE " + sender + " AND " + invid +
                                  " AND [Release] = 1");
                    try
                    {
                        while (dr.Read())
                        {
                            total = Convert.ToInt32(dr[0]);
                        }
                    }
                    catch (Exception)
                    {
                        throw;
                    }
                    finally
                    {
                        if (dr != null)
                        {
                            dr.Close();
                        }
                    }
                    break;
            }

            return total;
        }

        public List<InvoiceDao> LoadRange(string invcode, string customer, byte release, int startRow, int endRow)
        {
            string invid = "1=1";
            string sender = "1=1";
            
            if (!string.IsNullOrEmpty(invcode))
            {
                invid = "[InvId] ='" + invcode + "'";
            }

            if (!string.IsNullOrEmpty(customer))
            {
                sender = "[Sender] ='" + customer + "'";
            }

            List<Hashtable> lsHtbl = null;
            List<InvoiceDao>  lsInv = null;

            switch (release)
            {
                case 0://not release yet
                    lsHtbl = ExecuteArrayHastable(@"WITH MyOrders AS
                                                    (
                                                        SELECT InvId, StartDate, EndDate, StartPlace, EndPlace, ServiceName,
                                                        ROW_NUMBER() OVER (ORDER BY InvId) as 'RowNum'
                                                        FROM [Invoice] WITH (NOLOCK)
                                                        WHERE " + invid + " AND " + sender + " AND [Release] = 0" +
                                                        @")
                                                    SELECT * FROM MyOrders
                                                    WHERE RowNum BETWEEN " + startRow + " AND " + endRow);
                    lsInv = new List<InvoiceDao>(lsHtbl.Count);
                    foreach (Hashtable hashtable in lsHtbl)
                    {
                        lsInv.Add(new InvoiceDao(hashtable));
                    }
                    break;
                case 1://release
                    lsHtbl = ExecuteArrayHastable(@"WITH MyOrders AS
                                                    (
                                                        SELECT InvId, StartDate, EndDate, StartPlace, EndPlace, ServiceName,
                                                        ROW_NUMBER() OVER (ORDER BY InvId) as 'RowNum'
                                                        FROM [Invoice] WITH (NOLOCK)
                                                        WHERE " + invid + " AND " + sender + " AND [Release] = 1" +
                                                        @")
                                                    SELECT * FROM MyOrders
                                                    WHERE RowNum BETWEEN " + startRow + " AND " + endRow);
                    if (lsHtbl != null && lsHtbl.Count > 0)
                    {
                        lsInv = new List<InvoiceDao>(lsHtbl.Count);
                        foreach (Hashtable hashtable in lsHtbl)
                        {
                            lsInv.Add(new InvoiceDao(hashtable));
                        }
                    }
                    break;
            }

            return lsInv;
        }

        public InvoiceDao GetInvoice(string invid)
        {
            var htInv = ExecuteHashtable(string.Format("SELECT * FROM [Invoice] WITH (NOLOCK) WHERE [InvId] = '{0}'", invid));
            if (htInv != null)
            {
                return new InvoiceDao(htInv);
            }

            return null;
        }

        public List<InvoiceDao> GetAllInvoices()
        {
            var lsHtInv = ExecuteArrayHastable("SELECT * FROM [Invoice] WITH (NOLOCK)");

            if (lsHtInv != null && lsHtInv.Count > 0)
            {
                var lsInv = new List<InvoiceDao>(lsHtInv.Count);

                foreach (var inv in lsHtInv)
                {
                    lsInv.Add(new InvoiceDao(inv));
                }

                return lsInv;
            }

            return null;
        }

        public List<InvoiceDao> GetImportInvoices()
        {
            var lsHtInv = ExecuteArrayHastable("SELECT * FROM [Invoice] WITH (NOLOCK) WHERE Release = 0");

            if (lsHtInv != null && lsHtInv.Count > 0)
            {
                var lsInv = new List<InvoiceDao>(lsHtInv.Count);

                foreach (var inv in lsHtInv)
                {
                    lsInv.Add(new InvoiceDao(inv));
                }

                return lsInv;
            }

            return null;
        }

        public List<InvoiceDao> GetExportInvoices()
        {
            var lsHtInv = ExecuteArrayHastable("SELECT * FROM [Invoice] WITH (NOLOCK) WHERE Release = 1");

            if (lsHtInv != null && lsHtInv.Count > 0)
            {
                var lsInv = new List<InvoiceDao>(lsHtInv.Count);

                foreach (var inv in lsHtInv)
                {
                    lsInv.Add(new InvoiceDao(inv));
                }

                return lsInv;
            }

            return null;
        }

        public byte GetInvoiceStatus(string invid)
        {
            SqlDataReader dr = null;
            byte release = 0;

            try
            {
                dr = ExecuteReader(string.Format("SELECT [Release] FORM [Invoice] WHERE [InvId] = '{0}'", invid));
                while (dr.Read())
                {
                    release = byte.Parse(dr[0].ToString());
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (dr != null)
                {
                    dr.Close();
                }
            }

            return release;
        }

    }
}
